//
//  DataBaseHandle.m
//  B
//
//  Created by Sunpeng on 15/11/16.
//  Copyright © 2015年 wangzhan. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
@implementation DataBaseHandle
+(instancetype)ShareDataBaseHandle{
    static DataBaseHandle *dataHandle = nil;
    if (dataHandle == nil) {
        dataHandle = [[DataBaseHandle alloc]init];
    }
    return dataHandle;
    
  }
static sqlite3 *db = nil;
-(void)openDB{
    //声明数据库对象
    if (db!=nil) {
        NSLog(@"数据库已经打开");
        return;
    }
    //创建数据库文件路径
    //数据库文件存放在工程的沙盒里
    //参数1.指定获取沙盒中Document路径
    //参数2.设置路径搜索起始位置
    //参数3.获取路径是绝对路径还是相对路径--YES是绝对路径
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    //stringByAppendingPathComponent方法会自动在文件名后面加/
    NSString *sqlFilePath = [filePath stringByAppendingPathComponent:@"TravelData.sqlite"];
    //NSLog(@"++++++++++%@",sqlFilePath);
    //打开数据库
    //参数1.设置数据库文件路径
    //参数2.设置数据库对象
    //作用: 将数据库对象和文件进行关联并打开数据库
    int result = sqlite3_open(sqlFilePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"打开数据库成功");
    }else
    {
        NSLog(@"打开数据库失败");
    }

    
    
}
-(void)createImageCollect
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS ImageCollect(number INTEGER PRIMARY KEY AUTOINCREMENT,pic TEXT, text TEXT, idStr TEXT, typeStr TEXT, myIndex TEXT,count INTEGER)";
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表image成功");
    }else
    {
        NSLog(@"创建表失败");
    }
    
}
-(void)insertImageTable:(NSString *)pic and:(NSString *)text and:(NSString *)idStr and:(NSString *)typeStr and:(NSString *)index and:(NSInteger)count
{
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO ImageCollect(pic,text,idStr,typeStr,myIndex,count)VALUES('%@','%@','%@','%@','%@','%ld')",pic,text,idStr,typeStr,index,count];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"插入成功");
        //NSLog(@"77%@%@",pic,text);
    }else
    {
        //NSLog(@"插入失败");
    }
    
}
-(NSMutableArray *)selectImageCollectWithText
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = @"SELECT * FROM ImageCollect";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"查询准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *pic = sqlite3_column_text(stmt, 1);
            const unsigned char *text = sqlite3_column_text(stmt, 2);
            
            NSInteger numInteger = number;
            NSString *picStr = [NSString stringWithUTF8String:(const char *)pic];
            NSString *textStr = [NSString stringWithUTF8String:(const char *)text];
            
            [array addObject:textStr];
        }
    }
    else
    {
        //NSLog(@"查询准备失败");
    }
    return  array;
}
-(NSMutableArray *)selectAllImageCollect
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = @"SELECT * FROM ImageCollect";
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"查询准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int number = sqlite3_column_int(stmt, 0);
            const unsigned char *pic = sqlite3_column_text(stmt, 1);
            const unsigned char *text = sqlite3_column_text(stmt, 2);
            const unsigned char *idStr = sqlite3_column_text(stmt, 3);
            const unsigned char *typeStr = sqlite3_column_text(stmt, 4);
            const unsigned char *myIndex = sqlite3_column_text(stmt, 5);
            int count = sqlite3_column_int(stmt, 6);
            
            NSInteger numInteger = number;
            NSString *picStr = [NSString stringWithUTF8String:(const char *)pic];
            NSString *textStr = [NSString stringWithUTF8String:(const char *)text];
            NSString *idStr1 = [NSString stringWithUTF8String:(const char *)idStr];
            NSString *typeStr1 = [NSString stringWithUTF8String:(const char *)typeStr];
            NSString *indexStr = [NSString stringWithUTF8String:(const char *)myIndex];
            NSInteger countInt = count;
            MyCollectModel *collectM = [[MyCollectModel alloc]init];
            collectM.picStr = picStr;
            collectM.textStr = textStr;
            collectM.idString = idStr1;
            collectM.typeString = typeStr1;
            collectM.index = indexStr;
            collectM.count = countInt;
            [array addObject:collectM];
        }
    }
    else
    {
        //NSLog(@"查询准备失败");
    }
    return  array;
}
-(void)deleteImageTable
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM ImageCollect"];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"删除成功");
    }else
    {
        //NSLog(@"删除失败");
    }
    
}
-(void)deleteImageCollectTable:(MyCollectModel *)collectM
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM ImageCollect WHERE text = '%@'",collectM.textStr];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        //NSLog(@"删除成功");
    }else
    {
        //NSLog(@"删除失败");
    }
    
}





@end
